﻿Imports System
Imports System.IO
Imports System.Data.SqlServerCe
Imports System.Drawing
Imports System.Collections
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.Common
Imports System.Reflection

Public Class EqamDao
    Private db As DatabaseFactory = New DatabaseFactory
    Private pdaUtils As PdaUtils = New PdaUtils

    Public Function findEqamProjects(ByVal projectId As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT * FROM eqam_project WHERE 1=1 "

            If (projectId IsNot Nothing) Then
                SQL = SQL & " AND PROJECT_ID = @projectId"
                cmd.Parameters.AddWithValue("@projectId", projectId)
            End If

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findHeatNos(ByVal projectId As String, ByVal heatName As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT * FROM eqam_heat_number WHERE 1=1 "

            If (projectId IsNot Nothing And projectId <> "") Then
                SQL = SQL & " AND PROJECT_ID = @PROJECT_ID"
                cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
            End If

            If (heatName IsNot Nothing And heatName <> "") Then
                SQL = SQL & " AND HEAT_NUMBER_NAME = @HEAT_NUMBER_NAME"
                cmd.Parameters.AddWithValue("@HEAT_NUMBER_NAME", heatName)
            End If

            SQL = SQL & " ORDER BY HEAT_NUMBER_NAME "

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function getZoneSeq(ByVal zone As String, ByVal projectId As String) As Integer
        Dim rdr As SqlCeResultSet
        Dim index As Integer = 1
        rdr = findZones(zone, projectId)

        If (rdr.Read) Then
            index = rdr.GetDecimal(rdr.GetOrdinal("SEQ"))
        End If

        closeConnection()
        Return index
    End Function

    Public Function findZones(ByVal zone As String, ByVal projectId As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT * FROM eqam_zone WHERE 1=1 "

            If (zone IsNot Nothing And zone <> "") Then
                SQL = SQL & " AND ZONE = @ZONE"
                cmd.Parameters.AddWithValue("@ZONE", zone)
            End If

            If (projectId IsNot Nothing And projectId <> "") Then
                SQL = SQL & " AND PROJECT_ID = @PROJECT_ID"
                cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
            End If

            SQL = SQL & " ORDER BY ZONE "

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findSystemTypes(ByVal projectId As String, ByVal systemName As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT * FROM eqam_system_type WHERE 1=1 "

            If (projectId IsNot Nothing And projectId <> "") Then
                SQL = SQL & " AND PROJECT_ID = @PROJECT_ID"
                cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
            End If

            If (systemName IsNot Nothing And systemName <> "") Then
                SQL = SQL & " AND SYSTEM_TYPE_NAME = @SYSTEM_TYPE_NAME"
                cmd.Parameters.AddWithValue("@SYSTEM_TYPE_NAME", systemName)
            End If

            SQL = SQL & " ORDER BY SYSTEM_TYPE_NAME "

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findUserLog() As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT USER_NAME FROM eqam_user_log GROUP BY USER_NAME ORDER BY USER_NAME "

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function loadPreviousLoginId() As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT USER_NAME FROM eqam_user_log ORDER BY LOG_ID DESC"

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findWeldSize(ByVal welderId As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT SIZE FROM eqam_weld_size WHERE WELDER_ID = @WELDER_ID ORDER BY SIZE "

            cmd.CommandText = SQL
            cmd.Parameters.AddWithValue("@WELDER_ID", welderId)
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findGroupBySizes(ByVal projectId As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT SIZE FROM eqam_coupon WHERE PROJECT_ID = @PROJECT_ID GROUP BY SIZE ORDER BY SIZE "

            cmd.CommandText = SQL
            cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findGroupByThickness(ByVal projectId As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT THICKNESS FROM eqam_coupon WHERE PROJECT_ID = @PROJECT_ID GROUP BY THICKNESS ORDER BY THICKNESS "

            cmd.CommandText = SQL
            cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findRejectReason() As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT * FROM eqam_reject_reason ORDER BY REASON "

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findMaterials() As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT * FROM eqam_material ORDER BY MATERIAL "

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findJointTypes(ByVal jointTypeName As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT * FROM eqam_joint_type WHERE 1=1 "

            If (jointTypeName IsNot Nothing And jointTypeName <> "") Then
                SQL = SQL & " AND JOINT_TYPE_NAME = @JOINT_TYPE_NAME"
                cmd.Parameters.AddWithValue("@JOINT_TYPE_NAME", jointTypeName)
            End If

            SQL = SQL & " ORDER BY JOINT_TYPE_NAME "

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findTubeType(ByVal material As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT SIZE,THICKNESS FROM eqam_tube_type tt " & _
                  " LEFT JOIN eqam_material material ON material.MATERIAL = tt.MATERIAL" & _
                  " WHERE material.MATERIAL = @material ORDER BY SIZE,THICKNESS "

            cmd.CommandText = SQL
            cmd.Parameters.AddWithValue("@material", material)
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findEqamCoupons(ByVal welderUserId As String, ByVal couponType As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT coupon.COUPON_ID, coupon.COUPON_CODE, coupon.WELDER_USER_ID, coupon.COUPON_TYPE, coupon.COAX " & _
                  " , coupon.PROJECT_ID, coupon.STATUS_CODE, project.PROJECT_NAME " & _
                  " , coupon.MATERIAL, coupon.SIZE, coupon.THICKNESS " & _
                  " FROM eqam_coupon coupon LEFT JOIN eqam_project project ON coupon.PROJECT_ID = project.PROJECT_ID " & _
                  " WHERE 1=1 "

            If (welderUserId IsNot Nothing And welderUserId <> "") Then
                SQL = SQL & " AND WELDER_USER_ID = @welderUserId"
                cmd.Parameters.AddWithValue("@welderUserId", welderUserId)
            End If

            If (couponType IsNot Nothing And couponType <> "") Then
                SQL = SQL & " AND coupon.COUPON_TYPE = @couponType"
                cmd.Parameters.AddWithValue("@couponType", couponType)
            End If


            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function findCouponSystems(ByVal couponId As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT couponSystem.SYSTEM_ID, couponSystem.COUPON_ID, couponSystem.SYSTEM_TYPE_ID, systemType.SYSTEM_TYPE_NAME " & _
                  " FROM eqam_coupon_system couponSystem " & _
                  "     LEFT JOIN eqam_system_type systemType ON couponSystem.SYSTEM_TYPE_ID = systemType.SYSTEM_TYPE_ID  " & _
                  " WHERE 1=1 "

            If (couponId IsNot Nothing And couponId <> "") Then
                SQL = SQL & " AND couponSystem.COUPON_ID = @couponId"
                cmd.Parameters.AddWithValue("@couponId", couponId)
            End If

            SQL = SQL & " GROUP BY couponSystem.SYSTEM_ID, couponSystem.COUPON_ID, couponSystem.SYSTEM_TYPE_ID, systemType.SYSTEM_TYPE_NAME"

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Sub closeConnection()
        db.disconnect()
    End Sub

    Public Function findUsers(ByVal userName As String, ByVal userPassword As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim lstViewCust As New ListViewItem
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT u.USER_ID, u.USER_NAME, u.USER_FULL_NAME, u.USER_ROLE, welder.WELDER_NAME " & _
                  " , welder.WELDER_COMPANY_CODE, welder.WELDER_COMPANY_NAME" & _
                  " FROM eqam_user u LEFT JOIN eqam_welder welder ON u.USER_ID = welder.WELDER_ID WHERE u.USER_NAME = @username AND u.USER_PASSWORD = @userpassword"
            cmd.Parameters.AddWithValue("@username", userName)
            cmd.Parameters.AddWithValue("@userpassword", userPassword)

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function getThickness(ByVal size As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT * FROM eqam_tube_type WHERE SIZE = @size "
            cmd.Parameters.AddWithValue("@size", size)

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function getSize(ByVal thickness As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT * FROM eqam_tube_type WHERE THICKNESS = @thickness "
            cmd.Parameters.AddWithValue("@thickness", thickness)

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing
    End Function

    Public Function getTubeTypeId(ByVal material As String, ByVal size As String, ByVal thickness As String) As String
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim SQL As String = ""
        Dim tubeTypeId As String = ""
        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT * FROM eqam_tube_type WHERE SIZE = @size AND MATERIAL = @material AND THICKNESS = @thickness"
            cmd.Parameters.AddWithValue("@size", size)
            cmd.Parameters.AddWithValue("@material", material)
            cmd.Parameters.AddWithValue("@thickness", thickness)

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)
            If (rdr.Read) Then
                tubeTypeId = rdr.GetString(rdr.GetOrdinal("TUBE_TYPE_ID"))
            End If
            Return tubeTypeId
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return tubeTypeId
    End Function

    Public Function getTodayCouponCount(ByVal projectId As String) As Integer
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim SQL As String = ""
        Dim couponCount As Integer = 0
        Try
            db.connect()
            Dim dateAdd As String = pdaUtils.getCurrentDate
            cmd = db.conn.CreateCommand()
            SQL = "SELECT count(*) AS _COUNT FROM eqam_coupon WHERE DATE_ADD = @DATE_ADD AND PROJECT_ID = @PROJECT_ID"
            cmd.Parameters.AddWithValue("@DATE_ADD", dateAdd)
            cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)
            If (rdr.Read) Then
                couponCount = rdr.GetInt32(rdr.GetOrdinal("_COUNT"))
            End If

            Return couponCount
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return couponCount
    End Function

    Public Function getCouponCode(ByVal projectId As String) As String
        'coupon id = projc-welderid-050512-01
        Dim couponCode As String
        Dim couponCount As Integer = getTodayCouponCount(projectId)
        Dim rightNow As Date = Now
        couponCode = _PROJECT_NAME & "-" & _USER_NAME & "-" & rightNow.ToString("ddMMyy") & "-" & (couponCount + 1)

        Return couponCode
    End Function

    Public Function doCreateLogInLog(ByVal userName As String) As Boolean
        Dim cmd As SqlCeCommand
        db.connect()
        cmd = db.conn.CreateCommand
        cmd.CommandText = "INSERT INTO eqam_user_log(ADD_BY, DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY, USER_NAME)" & _
                          " VALUES (@ADD_BY, @DATE_ADD, @TIME_ADD, @DATE_UPDATE, @TIME_UPDATE, @UPDATE_BY, @USER_NAME)"

        Dim currentDate As Decimal = pdaUtils.getCurrentDate
        Dim currentTime As Decimal = pdaUtils.getCurrentTime
        'first line        
        cmd.Parameters.AddWithValue("@ADD_BY", _USER_ID)
        cmd.Parameters.AddWithValue("@DATE_ADD", currentDate)
        cmd.Parameters.AddWithValue("@TIME_ADD", currentTime)
        cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
        cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
        cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)
        cmd.Parameters.AddWithValue("@USER_NAME", userName)
        Try
            cmd.ExecuteNonQuery()
            db.disconnect()
            Return True
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
            Return False
        End Try
    End Function

    Public Function doCreateCoupon(ByVal userId As String, ByVal coax As String, ByVal projectId As String, ByVal material As String, ByVal size As String, ByVal thickness As String, ByVal isCoax As String, ByVal pipeTube As String, ByVal couponType As String, ByVal heatId1 As String, ByVal heatName1 As String, ByVal heatId2 As String, ByVal heatName2 As String, ByVal lv As Windows.Forms.ListView, ByVal parentCouponId As String, ByVal barcode As String, ByVal jointTypeCode1 As String, ByVal jointTypeName1 As String, ByVal jointTypeCode2 As String, ByVal jointTypeName2 As String) As Boolean
        Dim cmd As SqlCeCommand
        db.connect()
        cmd = db.conn.CreateCommand
        cmd.CommandText = "INSERT INTO eqam_coupon(COUPON_CODE, ADD_BY, DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY, STATUS_CODE" & _
                          " ,COUPON_TYPE, WELDER_USER_ID, PIPE_TUBE, IS_COAX" & _
                          " ,COAX, PROJECT_ID, MATERIAL, SIZE, THICKNESS, REMARKS" & _
                          " ,HEAT_NUMBER_ID_1, HEAT_NUMBER_NAME_1, HEAT_NUMBER_ID_2, HEAT_NUMBER_NAME_2, PARENT_COUPON_ID, BARCODE" & _
                          " ,JOINT_TYPE_CODE_1, JOINT_TYPE_NAME_1, JOINT_TYPE_CODE_2, JOINT_TYPE_NAME_2)" & _
                          " VALUES (@COUPON_CODE, @ADD_BY, @DATE_ADD, @TIME_ADD, @DATE_UPDATE, @TIME_UPDATE, @UPDATE_BY, @STATUS_CODE" & _
                          " ,@COUPON_TYPE, @WELDER_USER_ID, @PIPE_TUBE, @IS_COAX " & _
                          " ,@COAX, @PROJECT_ID, @MATERIAL, @SIZE, @THICKNESS, @REMARKS" & _
                          " ,@HEAT_NUMBER_ID_1, @HEAT_NUMBER_NAME_1, @HEAT_NUMBER_ID_2, @HEAT_NUMBER_NAME_2, @PARENT_COUPON_ID, @BARCODE" & _
                          " ,@JOINT_TYPE_CODE_1, @JOINT_TYPE_NAME_1, @JOINT_TYPE_CODE_2, @JOINT_TYPE_NAME_2)"

        Dim currentDate As Decimal = pdaUtils.getCurrentDate
        Dim currentTime As Decimal = pdaUtils.getCurrentTime
        'Dim tubeTypeId As String = Me.getTubeTypeId(material, size, thickness)

        Dim couponCode As String = ""
        If (couponType = Globals.COUPON_TYPE_IN) Then
            couponCode = getCouponCode(projectId)
        Else
            couponCode = _COUPON_CODE
        End If

        'first line
        cmd.Parameters.AddWithValue("@COUPON_CODE", couponCode)
        cmd.Parameters.AddWithValue("@ADD_BY", _USER_ID)
        cmd.Parameters.AddWithValue("@DATE_ADD", currentDate)
        cmd.Parameters.AddWithValue("@TIME_ADD", currentTime)
        cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
        cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
        cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)
        cmd.Parameters.AddWithValue("@STATUS_CODE", Globals.COUPON_STATUS_PENDING)
        'sec line
        cmd.Parameters.AddWithValue("@COUPON_TYPE", couponType)
        cmd.Parameters.AddWithValue("@WELDER_USER_ID", _USER_ID)
        cmd.Parameters.AddWithValue("@PIPE_TUBE", pipeTube)
        cmd.Parameters.AddWithValue("@IS_COAX", isCoax)
        'third line
        cmd.Parameters.AddWithValue("@COAX", coax)
        cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
        cmd.Parameters.AddWithValue("@MATERIAL", material)
        cmd.Parameters.AddWithValue("@SIZE", size)
        cmd.Parameters.AddWithValue("@THICKNESS", thickness)
        cmd.Parameters.AddWithValue("@REMARKS", "")

        cmd.Parameters.AddWithValue("@HEAT_NUMBER_ID_1", heatId1)
        cmd.Parameters.AddWithValue("@HEAT_NUMBER_NAME_1", heatName1)
        cmd.Parameters.AddWithValue("@HEAT_NUMBER_ID_2", heatId2)
        cmd.Parameters.AddWithValue("@HEAT_NUMBER_NAME_2", heatName2)
        cmd.Parameters.AddWithValue("@PARENT_COUPON_ID", parentCouponId)
        cmd.Parameters.AddWithValue("@BARCODE", barcode)

        cmd.Parameters.AddWithValue("@JOINT_TYPE_CODE_1", jointTypeCode1)
        cmd.Parameters.AddWithValue("@JOINT_TYPE_NAME_1", jointTypeName1)
        cmd.Parameters.AddWithValue("@JOINT_TYPE_CODE_2", jointTypeCode2)
        cmd.Parameters.AddWithValue("@JOINT_TYPE_NAME_2", jointTypeName2)

        Try
            cmd.ExecuteNonQuery()
            cmd.CommandText = "SELECT @@IDENTITY AS TEMPVALUE"
            Dim couponId As Integer = cmd.ExecuteScalar

            If (couponType = Globals.COUPON_TYPE_IN) Then
                If (lv IsNot Nothing) Then
                    ' save coupon system
                    Dim lstItem As ListViewItem
                    For Each lstItem In lv.Items
                        If (lstItem.Checked) Then
                            Dim systemId As String = lstItem.Tag

                            Dim sqlCouponSystem As String = "INSERT INTO eqam_coupon_system " & _
                            " (COUPON_ID, SYSTEM_TYPE_ID, ADD_BY, DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY)" & _
                            " VALUES (@COUPON_ID, @SYSTEM_TYPE_ID, @ADD_BY, @DATE_ADD, @TIME_ADD, @DATE_UPDATE, @TIME_UPDATE, @UPDATE_BY)"
                            cmd = db.conn.CreateCommand
                            cmd.CommandText = sqlCouponSystem
                            cmd.Parameters.AddWithValue("@COUPON_ID", couponId)
                            cmd.Parameters.AddWithValue("@SYSTEM_TYPE_ID", systemId)
                            cmd.Parameters.AddWithValue("@ADD_BY", _USER_ID)
                            cmd.Parameters.AddWithValue("@DATE_ADD", currentDate)
                            cmd.Parameters.AddWithValue("@TIME_ADD", currentTime)
                            cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
                            cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
                            cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)

                            'MsgBox(systemId & "-" & sqlCouponSystem)
                            cmd.ExecuteNonQuery()
                        End If
                    Next
                End If
            Else
                ' update coupon (in) to complete
                cmd = db.conn.CreateCommand
                cmd.CommandText = "UPDATE eqam_coupon SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY" & _
                            ", STATUS_CODE = @STATUS_CODE" & _
                            " WHERE COUPON_ID = @COUPON_ID"

                ' 1st line
                cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
                cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
                cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)

                ' 2nd line
                cmd.Parameters.AddWithValue("@STATUS_CODE", Globals.COUPON_STATUS_COMPLETED)
                cmd.Parameters.AddWithValue("@COUPON_ID", _COUPON_ID)

                'MsgBox(systemId & "-" & sqlCouponSystem)
                cmd.ExecuteNonQuery()
            End If

            db.disconnect()
            _SAVE_SUCCESSFUL_MESSAGE = "Coupon " & couponCode & " created."
            Return True
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
            Return False
        End Try
    End Function

    Public Function doUpdateCoupon(ByVal userId As String, ByVal couponId As String, ByVal coax As String, ByVal material As String, ByVal size As String, ByVal thickness As String, ByVal isCoax As String, ByVal pipeTube As String, ByVal heatId1 As String, ByVal heatName1 As String, ByVal heatId2 As String, ByVal heatName2 As String, ByVal lv As Windows.Forms.ListView, ByVal barcode As String, ByVal jointTypeCode1 As String, ByVal jointTypeName1 As String, ByVal jointTypeCode2 As String, ByVal jointTypeName2 As String) As Boolean
        Dim cmd As SqlCeCommand
        db.connect()
        cmd = db.conn.CreateCommand

        cmd.CommandText = "UPDATE eqam_coupon SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY" & _
                            ", PIPE_TUBE = @PIPE_TUBE, IS_COAX = @IS_COAX, COAX = @COAX" & _
                            ", MATERIAL = @MATERIAL, SIZE = @SIZE, THICKNESS = @THICKNESS" & _
                            ", HEAT_NUMBER_ID_1 = @HEAT_NUMBER_ID_1, HEAT_NUMBER_NAME_1 = @HEAT_NUMBER_NAME_1" & _
                            ", HEAT_NUMBER_ID_2 = @HEAT_NUMBER_ID_2, HEAT_NUMBER_NAME_2 = @HEAT_NUMBER_NAME_2, BARCODE = @BARCODE" & _
                            ", JOINT_TYPE_CODE_1 = @JOINT_TYPE_CODE_1, JOINT_TYPE_NAME_1 = @JOINT_TYPE_NAME_1, JOINT_TYPE_CODE_2 = @JOINT_TYPE_CODE_2, JOINT_TYPE_NAME_2 = @JOINT_TYPE_NAME_2" & _
                            " WHERE COUPON_ID = @COUPON_ID"

        Dim currentDate As Decimal = pdaUtils.getCurrentDate
        Dim currentTime As Decimal = pdaUtils.getCurrentTime

        ' 1st line
        cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
        cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
        cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)

        ' 2nd line
        cmd.Parameters.AddWithValue("@PIPE_TUBE", pipeTube)
        cmd.Parameters.AddWithValue("@IS_COAX", isCoax)
        cmd.Parameters.AddWithValue("@COAX", coax)

        ' 3nd line
        cmd.Parameters.AddWithValue("@MATERIAL", material)
        cmd.Parameters.AddWithValue("@SIZE", size)
        cmd.Parameters.AddWithValue("@THICKNESS", thickness)

        cmd.Parameters.AddWithValue("@HEAT_NUMBER_ID_1", heatId1)
        cmd.Parameters.AddWithValue("@HEAT_NUMBER_NAME_1", heatName1)
        cmd.Parameters.AddWithValue("@HEAT_NUMBER_ID_2", heatId2)
        cmd.Parameters.AddWithValue("@HEAT_NUMBER_NAME_2", heatName2)
        cmd.Parameters.AddWithValue("@BARCODE", barcode)

        cmd.Parameters.AddWithValue("@JOINT_TYPE_CODE_1", jointTypeCode1)
        cmd.Parameters.AddWithValue("@JOINT_TYPE_NAME_1", jointTypeName1)
        cmd.Parameters.AddWithValue("@JOINT_TYPE_CODE_2", jointTypeCode2)
        cmd.Parameters.AddWithValue("@JOINT_TYPE_NAME_2", jointTypeName2)

        cmd.Parameters.AddWithValue("@COUPON_ID", couponId)

        Try
            cmd.ExecuteNonQuery()

            cmd = db.conn.CreateCommand
            cmd.CommandText = "DELETE FROM eqam_coupon_system WHERE COUPON_ID = @COUPON_ID"
            cmd.Parameters.AddWithValue("@COUPON_ID", couponId)
            cmd.ExecuteNonQuery()

            ' save coupon system
            Dim lstItem As ListViewItem
            For Each lstItem In lv.Items
                If (lstItem.Checked) Then
                    Dim systemId As String = lstItem.Tag

                    Dim sqlCouponSystem As String = "INSERT INTO eqam_coupon_system " & _
                    " (COUPON_ID, SYSTEM_TYPE_ID, ADD_BY, DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY)" & _
                    " VALUES (@COUPON_ID, @SYSTEM_TYPE_ID, @ADD_BY, @DATE_ADD, @TIME_ADD, @DATE_UPDATE, @TIME_UPDATE, @UPDATE_BY)"
                    cmd = db.conn.CreateCommand
                    cmd.CommandText = sqlCouponSystem
                    cmd.Parameters.AddWithValue("@COUPON_ID", couponId)
                    cmd.Parameters.AddWithValue("@SYSTEM_TYPE_ID", systemId)
                    cmd.Parameters.AddWithValue("@ADD_BY", _USER_ID)
                    cmd.Parameters.AddWithValue("@DATE_ADD", currentDate)
                    cmd.Parameters.AddWithValue("@TIME_ADD", currentTime)
                    cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
                    cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
                    cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)

                    'MsgBox(systemId & "-" & sqlCouponSystem)
                    cmd.ExecuteNonQuery()
                End If
            Next

            db.disconnect()
            _SAVE_SUCCESSFUL_MESSAGE = "Update Successfully."
            Return True
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
            Return False
        End Try
    End Function

    Public Function updateCouponStatus(ByVal remark As String, ByVal rejectReason As String, ByVal status As String, ByVal couponId As String) As Boolean
        Dim cmd As SqlCeCommand
        db.connect()
        cmd = db.conn.CreateCommand
        cmd.CommandText = "UPDATE eqam_coupon SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY, STATUS_CODE = @STATUS_CODE, REMARKS = @REMARKS, REJECT_REASON = @REJECT_REASON" & _
                          " WHERE COUPON_ID = @COUPON_ID"

        Dim currentDate As Decimal = pdaUtils.getCurrentDate
        Dim currentTime As Decimal = pdaUtils.getCurrentTime

        cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
        cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
        cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)
        cmd.Parameters.AddWithValue("@STATUS_CODE", status)
        cmd.Parameters.AddWithValue("@COUPON_ID", couponId)
        cmd.Parameters.AddWithValue("@REMARKS", remark)
        cmd.Parameters.AddWithValue("@REJECT_REASON", rejectReason)

        Try
            cmd.ExecuteNonQuery()
            db.disconnect()
            _SAVE_SUCCESSFUL_MESSAGE = "Update Successfully."
            Return True
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
            Return False
        End Try
    End Function

    Function findCouponCreateDateByGroup() As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT DATE_ADD FROM eqam_coupon GROUP BY DATE_ADD ORDER BY DATE_ADD "

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing

    End Function

    Function findCouponNoByGroup() As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT COUPON_CODE FROM eqam_coupon ORDER BY COUPON_CODE "

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing

    End Function

    Function findWeldSeq(ByVal projectId As String, ByVal zone As String) As SqlCeResultSet
        Dim cmd As SqlCeCommand
        Dim rdr As SqlCeResultSet
        Dim SQL As String = ""

        Try
            db.connect()
            cmd = db.conn.CreateCommand()
            SQL = "SELECT SEQ_ID, PROJECT_ID, ZONE, SEQ, BARCODE" & _
                  "     FROM eqam_weld_seq WHERE 1=1 "

            If (projectId IsNot Nothing And projectId <> "") Then
                SQL = SQL & " AND PROJECT_ID = @PROJECT_ID"
                cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
            End If

            If (zone IsNot Nothing And zone <> "") Then
                SQL = SQL & " AND ZONE = @ZONE"
                cmd.Parameters.AddWithValue("@ZONE", zone)
            End If

            SQL = SQL & " ORDER BY SEQ "

            cmd.CommandText = SQL
            rdr = cmd.ExecuteResultSet(ResultSetOptions.Scrollable)

            Return rdr
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
        End Try
        Return Nothing

    End Function

    Public Function saveWeldSeq(ByVal projectId As String, ByVal zone As String, ByVal barcode As String, ByVal seq As String) As Boolean
        Dim cmd As SqlCeCommand
        db.connect()
        cmd = db.conn.CreateCommand
        cmd.CommandText = "INSERT INTO eqam_weld_seq(ADD_BY, DATE_ADD, TIME_ADD, DATE_UPDATE, TIME_UPDATE, UPDATE_BY" & _
                            ", PROJECT_ID, ZONE, SEQ, BARCODE)" & _
                          " VALUES (@ADD_BY, @DATE_ADD, @TIME_ADD, @DATE_UPDATE, @TIME_UPDATE, @UPDATE_BY" & _
                            ", @PROJECT_ID, @ZONE, @SEQ, @BARCODE)"

        Dim currentDate As Decimal = pdaUtils.getCurrentDate
        Dim currentTime As Decimal = pdaUtils.getCurrentTime
        'first line        
        cmd.Parameters.AddWithValue("@ADD_BY", _USER_ID)
        cmd.Parameters.AddWithValue("@DATE_ADD", currentDate)
        cmd.Parameters.AddWithValue("@TIME_ADD", currentTime)
        cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
        cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
        cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)

        cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
        cmd.Parameters.AddWithValue("@ZONE", zone)
        cmd.Parameters.AddWithValue("@SEQ", seq)
        cmd.Parameters.AddWithValue("@BARCODE", barcode)
        Try
            cmd.ExecuteNonQuery()

            Dim sqlCouponSystem As String = "UPDATE eqam_zone " & _
                    " SET DATE_UPDATE = @DATE_UPDATE, TIME_UPDATE = @TIME_UPDATE, UPDATE_BY = @UPDATE_BY, SEQ = @SEQ WHERE ZONE = @ZONE AND PROJECT_ID = @PROJECT_ID"
            cmd = db.conn.CreateCommand
            cmd.CommandText = sqlCouponSystem
            cmd.Parameters.AddWithValue("@DATE_UPDATE", currentDate)
            cmd.Parameters.AddWithValue("@TIME_UPDATE", currentTime)
            cmd.Parameters.AddWithValue("@UPDATE_BY", _USER_ID)
            cmd.Parameters.AddWithValue("@SEQ", seq + 1)
            cmd.Parameters.AddWithValue("@ZONE", zone)
            cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
            cmd.ExecuteNonQuery()

            db.disconnect()
            Return True
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
            Return False
        End Try
    End Function

    Public Function deleteWeldSeq(ByVal projectId As String, ByVal zone As String, ByVal seqs As ArrayList) As Boolean
        Dim cmd As SqlCeCommand
        Dim SQL As String
        db.connect()
        cmd = db.conn.CreateCommand
        SQL = "DELETE FROM eqam_weld_seq WHERE 1=1"

        If (projectId IsNot Nothing And projectId <> "") Then
            SQL = SQL & " AND PROJECT_ID = @PROJECT_ID"
            cmd.Parameters.AddWithValue("@PROJECT_ID", projectId)
        End If

        If (zone IsNot Nothing And zone <> "") Then
            SQL = SQL & " AND ZONE = @ZONE"
            cmd.Parameters.AddWithValue("@ZONE", zone)
        End If

        If (seqs IsNot Nothing) Then
            Dim seq As Integer
            Dim idx As Integer = 0
            If (seqs.Count > 0) Then
                SQL = SQL & " AND ("
                For Each seq In seqs
                    If (idx > 0) Then
                        SQL = SQL & " OR "
                    End If
                    SQL = SQL & " SEQ_ID = " & seq
                    idx = idx + 1
                Next
                SQL = SQL & ")"
            End If
        End If

        cmd.CommandText = SQL

        Try
            cmd.ExecuteNonQuery()
            db.disconnect()
            Return True
        Catch ex As Exception
            db.disconnect()
            MsgBox(ex.Message, MsgBoxStyle.Critical, "EQAM")
            Return False
        End Try
    End Function
End Class
